<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class M_search Extends MY_Model
{

	/*****************************************************************************
		Start for Inquiries
	*****************************************************************************/
	public function get_inquiries($start,$limit,$where)
	{
		$start = is_numeric($start)? intval($start) : 0;
		$limit = is_numeric($limit) ? intval($limit) : 0;
		
		$sql = "SELECT  concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
						date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
						l.level_desc,
						e.e_id as enrollment_id,
						l.level_desc as level_code
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE e.e_ay_id = ?
						AND e.e_enrollee_type = ?
						AND e.af_paid = ?
						AND e.charge_to = ?";
		if(in_array(strtolower($where),range('a','z')))
		{
			$sql .= "AND p.lastname like ?
					 ORDER BY e.e_created_at,p.lastname,p.firstname,p.middlename,e.e_grade_level
					 LIMIT {$limit} , {$start}";
			$query = $this->db->query($sql,array($this->c_sy->id,'new','no','no',$where.'%'));
		}else{
			$sql .= "ORDER BY e.e_created_at,p.lastname,p.firstname,p.middlename,e.e_grade_level
					 LIMIT {$limit} , {$start}";
			$query = $this->db->query($sql,array($this->c_sy->id,'new','no','no'));
		}
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}

	public function search_inquiries($ajax = FALSE,$data)
	{
		if($ajax == FALSE)
		{
			$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
						date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
						level_desc,
						e.e_id as enrollment_id,
						l.level_desc as level_code,
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE e.e_id = ?
						AND e.e_enrollee_type = 'new'
						AND e.e_ay_id = ?
						AND e.af_paid = ?
						AND e.charge_to = ?";
						
			$query = $this->db->query($sql,array($data,$this->c_sy->id,'no','no'));
		}else{
			$sql = "SELECT  e.e_student_id as value,
						concat_ws('','[',p.gender,'] ',p.lastname,', ',p.firstname,' ',p.middlename,' | ',e.e_student_id,' | ',l.level_desc) as label,
						e.e_id as id,
						concat_ws('', e.e_id,' - ',p.lastname,', ',p.firstname,' ',p.middlename ) as format
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE 		
						(   p.firstname LIKE ? 
							OR p.lastname LIKE ? 
							OR p.middlename LIKE ? 
							OR e.e_student_id LIKE ? 
							OR concat_ws('',p.firstname,' ',p.middlename,' ',p.lastname) LIKE ?
							OR concat_ws('',p.lastname,' ',p.firstname,' ',p.middlename) LIKE ?
						) 
						AND e.e_enrollee_type = 'new'
						AND e.e_ay_id = ?
						AND e.af_paid = ?
						AND e.charge_to = ?";
						
			$query = $this->db->query($sql,array('%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 $this->c_sy->id,
												 'no',
												 'no'));
		}

		return $query-> num_rows() >= 1 ? $query->result() : FALSE;
	}

	public function count_inquiries($where)
	{
			$sql = "SELECT count(e.e_id) as num_rows
							FROM enrollments e
							LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
							LEFT JOIN levels l ON e.e_grade_level = l.level_id
							WHERE e.e_ay_id = ?
							AND e.e_enrollee_type = ?
							AND e.af_paid = ?
							AND e.charge_to = ?";
			if(in_array($where,range('a','z')))
			{	
				$sql .= " AND p.lastname like ?";
				$query = $this->db->query($sql,array($this->c_sy->id,'new','no','no',$where.'%'));
			}else{
				$query = $this->db->query($sql,array($this->c_sy->id,'new','no','no'));
			}
			return $query->num_rows() >= 1 ? $query->row()->num_rows : FALSE;
	}
	/*****************************************************************************
		End for Inquiries
	*****************************************************************************/

	/*****************************************************************************
		Start for Applicants
	*****************************************************************************/
	public function get_applicants($start,$limit,$where)
	{
		$start = is_numeric($start)? intval($start) : 0;
		$limit = is_numeric($limit) ? intval($limit) : 0;
		
		$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
						date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
						l.level_desc,
						e.e_id as enrollment_id,
						e.charge_to,
						e.af_paid,
						l.level_desc as level_code
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE (e.af_paid = ? OR e.charge_to = ?)
						AND e.e_ay_id = ?
						AND e.e_enrollee_type = ?";
		if(in_array(strtolower($where),range('a','z')))
		{
			$sql .= "AND p.lastname like ?
					 ORDER BY e.e_created_at,p.lastname,p.firstname,p.middlename,e.e_grade_level
					 LIMIT {$limit} , {$start}";
			$query = $this->db->query($sql,array('yes','yes',$this->c_sy->id,'new',$where.'%'));
		}else{
			$sql .= "ORDER BY e.e_created_at,p.lastname,p.firstname,p.middlename,e.e_grade_level
					 LIMIT {$limit} , {$start}";
			$query = $this->db->query($sql,array('yes','yes',$this->c_sy->id,'new'));
		}
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}

	public function search_applicants($ajax = FALSE,$data)
	{
		if($ajax == FALSE)
		{
			$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
						date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
						level_desc,
						e.e_id as enrollment_id,
						e.charge_to,
						l.level_desc as level_code
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE (e.af_paid = ? OR e.charge_to = ?)
						AND e.e_id = ?
						AND e.e_enrollee_type = 'new'
						AND e.e_ay_id = ?";
						
			$query = $this->db->query($sql,array('yes','yes',$data,$this->c_sy->id));
		}else{
			$sql = "SELECT  e.e_student_id as value,
						concat_ws('','[',p.gender,'] ',p.lastname,', ',p.firstname,' ',p.middlename,' | ',e.e_student_id,' | ',l.level_desc) as label,
						e.e_id as id,
						concat_ws('', e.e_id,' - ',p.lastname,', ',p.firstname,' ',p.middlename ) as format
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE 		
						(   p.firstname LIKE ? 
							OR p.lastname LIKE ? 
							OR p.middlename LIKE ? 
							OR e.e_student_id LIKE ? 
							OR concat_ws('',p.firstname,' ',p.middlename,' ',p.lastname) LIKE ?
							OR concat_ws('',p.lastname,' ',p.firstname,' ',p.middlename) LIKE ?
						) 
						AND (e.af_paid = ? OR e.charge_to = ?)
						AND e.e_enrollee_type = 'new'
						AND e.e_ay_id = ?";
						
			$query = $this->db->query($sql,array('%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 'yes',
												 'yes',
												 $this->c_sy->id));
		}

		return $query-> num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function count_applicants($where)
	{
		$sql = "SELECT count(e.e_id) as num_rows
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE (e.af_paid = ? OR e.charge_to = ?)
						AND e.e_ay_id = ?
						AND e.e_enrollee_type = ?";
		if(in_array($where,range('a','z')))
		{	
			$sql .= " AND p.lastname like ?";
			$query = $this->db->query($sql,array('yes','yes',$this->c_sy->id,'new',$where.'%'));
		}else{
			$query = $this->db->query($sql,array('yes','yes',$this->c_sy->id,'new'));
		}
		return $query->num_rows() >= 1 ? $query->row()->num_rows : FALSE;
	}
	
	/*****************************************************************************
		End for Applicants
	*****************************************************************************/

	/*****************************************************************************
		Start for Enrollees
	*****************************************************************************/

	public function approved_enrollees($start,$limit,$where)
	{
		$start = is_numeric($start)? intval($start) : 0;
		$limit = is_numeric($limit) ? intval($limit) : 0;
		
			$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
							p.nationality,
							date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
							e.e_enrollee_type,
							level_desc,
							e.e_id as enrollment_id,
							e.charge_to,
							e.e_student_id as idno,
							l.level_desc as level_code,
							p.email
							FROM enrollments e
							LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
							LEFT JOIN levels l ON e.e_grade_level = l.level_id
							WHERE (e.moved_to = ? OR e.guidance_approved = ?)
							AND e.registrar_approved = ?
							AND e.e_ay_id = ?";
			if(in_array(strtolower($where),range('a','z')))
			{
				$sql .= "AND p.lastname like ?
						 ORDER BY p.lastname,p.firstname,p.middlename,e.e_grade_level,e.e_created_at
						 LIMIT {$limit} , {$start}";
				$query = $this->db->query($sql,array('yes',1,0,$this->c_sy->id,$where.'%'));
			}else{
				$sql .= "ORDER BY p.lastname,p.firstname,p.middlename,e.e_grade_level,e.e_created_at
						 LIMIT {$limit} , {$start}";
				$query = $this->db->query($sql,array('yes',1,0,$this->c_sy->id));
			}
			return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function search_approved_enrollees($ajax = FALSE,$data)
	{
		if($ajax == FALSE)
		{
			$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
						p.nationality,
						date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
						e.e_enrollee_type,
						level_desc,
						e.e_id as enrollment_id,
						e.charge_to,
						l.level_desc as level_code,
						p.email
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE (e.moved_to = ? OR e.guidance_approved = ?)
						AND e.registrar_approved = ?
						AND e.e_id = ?
						AND e.e_ay_id = ?";
						
			$query = $this->db->query($sql,array('yes',1,0,$data,$this->c_sy->id));
		}else{
			$sql = "SELECT  e.e_student_id as value,
						concat_ws('','[',p.gender,'] ',p.lastname,', ',p.firstname,' ',p.middlename,' | ',e.e_student_id,' | ',l.level_desc) as label,
						e.e_id as id,
						concat_ws('', e.e_id,' - ',p.lastname,', ',p.firstname,' ',p.middlename) as format
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE 		
						(   p.firstname LIKE ? 
							OR p.lastname LIKE ? 
							OR p.middlename LIKE ? 
							OR e.e_student_id LIKE ? 
							OR concat_ws('',p.firstname,' ',p.middlename,' ',p.lastname) LIKE ?
							OR concat_ws('',p.lastname,' ',p.firstname,' ',p.middlename) LIKE ?
						) 
						AND (e.moved_to = ? OR e.guidance_approved = ?)
						AND e.registrar_approved = ?
						AND e.e_ay_id = ?";
						
			$query = $this->db->query($sql,array('%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 'yes',
												 1,
												 0,
												 $this->c_sy->id));
		}
		
		return $query-> num_rows() >= 1 ? $query->result() : FALSE;

	}
	
	public function count_approved_enrollees($where)
	{
		$sql = "SELECT count(e.e_id) as num_rows
				FROM enrollments e
				LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
				LEFT JOIN levels l ON e.e_grade_level = l.level_id
				WHERE (e.moved_to = ? OR e.guidance_approved = ?)
				AND e.registrar_approved = ?
				AND e.e_ay_id = ?";
		if(in_array($where,range('a','z')))
		{	
			$sql .= " AND p.lastname like ?";
			$query = $this->db->query($sql,array('yes',1,0,$this->c_sy->id,$where.'%'));
		}else{
			$query = $this->db->query($sql,array('yes',1,0,$this->c_sy->id));
		}
		return $query->num_rows() >= 1 ? $query->row()->num_rows : FALSE;
	}

	/*****************************************************************************
		End for Enrollees
	*****************************************************************************/

	/*****************************************************************************
		Start for Registrar Approved
	*****************************************************************************/

	public function registrar_approved($start,$limit,$where)
	{
		$start = is_numeric($start)? intval($start) : 0;
		$limit = is_numeric($limit) ? intval($limit) : 0;
		
			$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
							p.nationality,
							date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
							e.e_enrollee_type,
							level_desc,
							e.e_id as enrollment_id,
							e.e_student_id as idno,
							l.level_desc as level_code,
							p.email
							FROM enrollments e
							LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
							LEFT JOIN levels l ON e.e_grade_level = l.level_id
							WHERE (e.moved_to = ? OR e.guidance_approved = ?)
							AND e.registrar_approved = ?
							AND e.e_ay_id = ?";
			if(in_array(strtolower($where),range('a','z')))
			{
				$sql .= "AND p.lastname like ?
						 ORDER BY p.lastname,p.firstname,p.middlename,e.e_grade_level,e.e_created_at
					     LIMIT {$limit} , {$start}";
				$query = $this->db->query($sql,array('yes',1,1,$this->c_sy->id,$where.'%'));
			}else{
				$sql .= "ORDER BY p.lastname,p.firstname,p.middlename,e.e_grade_level,e.e_created_at
						 LIMIT {$limit} , {$start}";
				$query = $this->db->query($sql,array('yes',1,1,$this->c_sy->id));
			}
			return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function search_registrar_approved($ajax = FALSE,$data)
	{
		if($ajax == FALSE)
		{
			$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
						p.nationality,
						date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
						e.e_enrollee_type,
						level_desc,
						e.e_id as enrollment_id,
						e.e_student_id as idno,
						l.level_desc as level_code,
						p.email
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE (e.moved_to = ? OR e.guidance_approved = ?)
						AND e.registrar_approved = ?
						AND e.e_id = ?
						AND e.e_ay_id = ?";
						
			$query = $this->db->query($sql,array('yes',1,1,$data,$this->c_sy->id));
		}else{
			$sql = "SELECT  e.e_student_id as value,
						concat_ws('','[',p.gender,'] ',p.lastname,', ',p.firstname,' ',p.middlename,' | ',e.e_student_id,' | ',l.level_desc) as label,
						e.e_id as id,
						concat_ws('', e.e_id,' - ',p.lastname,', ',p.firstname,' ',p.middlename ) as format
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE 		
						(   p.firstname LIKE ? 
							OR p.lastname LIKE ? 
							OR p.middlename LIKE ? 
							OR e.e_student_id LIKE ? 
							OR concat_ws('',p.firstname,' ',p.middlename,' ',p.lastname) LIKE ?
							OR concat_ws('',p.lastname,' ',p.firstname,' ',p.middlename) LIKE ?
						) 
						AND (e.moved_to = ? OR e.guidance_approved = ?)
						AND e.registrar_approved = ?
						AND e.e_ay_id = ?";
						
			$query = $this->db->query($sql,array('%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 'yes',
												 1,
												 1,
												 $this->c_sy->id));
		}
		
		return $query-> num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function count_registrar_approved($where)
	{
		$sql = "SELECT count(e.e_id) as num_rows
				FROM enrollments e
				LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
				LEFT JOIN levels l ON e.e_grade_level = l.level_id
				WHERE (e.moved_to = ? OR e.guidance_approved = ?)
				AND e.registrar_approved = ?
				AND e.e_ay_id = ?";
		
		if(in_array($where,range('a','z')))
		{
			$sql .= " AND p.lastname like ?";
			$query = $this->db->query($sql,array('yes',1,1,$this->c_sy->id,$where.'%'));
		}else{
			$query = $this->db->query($sql,array('yes',1,1,$this->c_sy->id));
		}
		return $query->num_rows() >= 1 ? $query->row()->num_rows : FALSE;
	}

	/*****************************************************************************
		End for Registrar Approved
	*****************************************************************************/

	/*****************************************************************************
		Start for Approved and Enrolled student. Those with payments.
	*****************************************************************************/
	public function approved_enrolled($start,$limit,$where)
	{
		$start = is_numeric($start) ? intval($start) : 0;
		$limit = is_numeric($limit) ? intval($limit) : 0;
		
		if($this->c_e_gl != '' && !empty($this->c_e_gl))
		{
			$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
					p.nationality,
					date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
					e.e_enrollee_type,
					level_desc,
					e.e_id as enrollment_id,
					e.e_student_id as idno,
					l.level_desc as level_code
					FROM enrollments e
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					WHERE e.e_grade_level IN ({$this->c_e_gl})
					AND e.e_ay_id = ?
					AND e.guidance_approved = ?
					AND e.e_registered = ? ";
			if(in_array(strtolower($where),range('a','z')))
			{
				$sql .= "AND p.lastname like ?
						 ORDER BY p.lastname,p.firstname,p.middlename,e.e_grade_level,e.e_created_at 
					     LIMIT {$limit} , {$start}";
				$query = $this->db->query($sql,array($this->c_sy->id,1,1,$where.'%'));
			}else{
				$sql .= "ORDER BY p.lastname,p.firstname,p.middlename,e.e_grade_level,e.e_created_at 
						 LIMIT {$limit} , {$start}";
				$query = $this->db->query($sql,array($this->c_sy->id,1,1));
			}
			return $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		}else{
		
			$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
					p.nationality,
					date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
					e.e_enrollee_type,
					level_desc,
					e.e_id as enrollment_id,
					e.e_student_id as idno,
					l.level_desc as level_code
					FROM enrollments e
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					WHERE e.e_ay_id = ?
					AND e.guidance_approved = ?
					AND e.e_registered = ? ";
			if(in_array(strtolower($where),range('a','z')))
			{
				$sql .= "AND p.lastname like ?
						 ORDER BY p.lastname,p.firstname,p.middlename,e.e_grade_level,e.e_created_at 
					     LIMIT {$limit} , {$start}";
				$query = $this->db->query($sql,array($this->c_sy->id,1,1,$where.'%'));
			}else{
				$sql .= "ORDER BY p.lastname,p.firstname,p.middlename,e.e_grade_level,e.e_created_at 
						 LIMIT {$limit} , {$start}";
				$query = $this->db->query($sql,array($this->c_sy->id,1,1));
			}
			return $query->num_rows() >= 1 ? $query->result() : FALSE;
		
		}
	}
	
	public function search_approved_enrolled($ajax = FALSE,$data)
	{
		if($this->c_e_gl != '' && !empty($this->c_e_gl)){
		
			if($ajax == FALSE)
			{
				$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
							p.nationality,
							date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
							e.e_enrollee_type,
							level_desc,
							e.e_id as enrollment_id,
							e.e_student_id as idno,
							l.level_desc as level_code
							FROM enrollments e
							LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
							LEFT JOIN levels l ON e.e_grade_level = l.level_id
							WHERE e.guidance_approved = ?
							AND e.e_grade_level IN ({$this->c_e_gl})
							AND e.e_registered = ?
							AND e.e_id = ?
							AND e.e_ay_id = ?
							ORDER BY p.lastname
							";
							
				$query = $this->db->query($sql,array(1,1,$data,$this->c_sy->id));
			}else{
				$sql = "SELECT  e.e_student_id as value,
							concat_ws('','[',p.gender,'] ',p.lastname,', ',p.firstname,' ',p.middlename,' | ',e.e_student_id,' | ',l.level_desc) as label,
							e.e_id as id,
							concat_ws('', e.e_id,' - ',p.lastname,', ',p.firstname,' ',p.middlename ) as format
							FROM enrollments e
							LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
							LEFT JOIN levels l ON e.e_grade_level = l.level_id
							WHERE 		
							(   p.firstname LIKE ? 
								OR p.lastname LIKE ? 
								OR p.middlename LIKE ? 
								OR e.e_student_id LIKE ? 
								OR concat_ws('',p.firstname,' ',p.middlename,' ',p.lastname) LIKE ?
								OR concat_ws('',p.lastname,' ',p.firstname,' ',p.middlename) LIKE ?
							) 
							AND e.e_grade_level IN ({$this->c_e_gl})
							AND e.guidance_approved = ?
							AND e.e_registered = ?
							AND e.e_ay_id = ?
							ORDER BY p.lastname
							";
							
				$query = $this->db->query($sql,array('%'.$data.'%',
													 '%'.$data.'%',
													 '%'.$data.'%',
													 '%'.$data.'%',
													 '%'.$data.'%',
													 '%'.$data.'%',
													 1,
													 1,
													 $this->c_sy->id));
			}
		
		}else{
		
			if($ajax == FALSE)
			{
				$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
							p.nationality,
							date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
							e.e_enrollee_type,
							level_desc,
							e.e_id as enrollment_id,
							e.e_student_id as idno,
							l.level_desc as level_code
							FROM enrollments e
							LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
							LEFT JOIN levels l ON e.e_grade_level = l.level_id
							WHERE e.guidance_approved = ?
							AND e.e_registered = ?
							AND e.e_id = ?
							AND e.e_ay_id = ?
							ORDER BY p.lastname
							";
							
				$query = $this->db->query($sql,array(1,1,$data,$this->c_sy->id));
			}else{
				$sql = "SELECT  e.e_student_id as value,
							concat_ws('','[',p.gender,'] ',p.lastname,', ',p.firstname,' ',p.middlename,' | ',e.e_student_id,' | ',l.level_desc) as label,
							e.e_id as id,
							concat_ws('', e.e_id,' - ',p.lastname,', ',p.firstname,' ',p.middlename ) as format
							FROM enrollments e
							LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
							LEFT JOIN levels l ON e.e_grade_level = l.level_id
							WHERE 		
							(   p.firstname LIKE ? 
								OR p.lastname LIKE ? 
								OR p.middlename LIKE ? 
								OR e.e_student_id LIKE ? 
								OR concat_ws('',p.firstname,' ',p.middlename,' ',p.lastname) LIKE ?
								OR concat_ws('',p.lastname,' ',p.firstname,' ',p.middlename) LIKE ?
							) 
							AND e.guidance_approved = ?
							AND e.e_registered = ?
							AND e.e_ay_id = ?
							ORDER BY p.lastname
							";
							
				$query = $this->db->query($sql,array('%'.$data.'%',
													 '%'.$data.'%',
													 '%'.$data.'%',
													 '%'.$data.'%',
													 '%'.$data.'%',
													 '%'.$data.'%',
													 1,
													 1,
													 $this->c_sy->id));
			}
		
		}
	
		return $query-> num_rows() >= 1 ? $query->result() : FALSE;	
	}
	
	public function count_approved_enrolled($where)
	{
		if($this->c_e_gl != '' && !empty($this->c_e_gl)){
			$sql = "SELECT count(e.e_id) as num_rows
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE e.e_grade_level IN ({$this->c_e_gl})
						AND e.e_ay_id = ?
						AND e.guidance_approved = ?
						AND e.e_registered = ?";
			if(in_array($where,range('a','z')))
			{
				$sql .= " AND p.lastname like ?";
				$query = $this->db->query($sql,array($this->c_sy->id,1,1,$where.'%'));
			}else{
				$query = $this->db->query($sql,array($this->c_sy->id,1,1));
			}
			return $query->num_rows() >= 1 ? $query->row()->num_rows : FALSE;
		}else{
			$sql = "SELECT count(e.e_id) as num_rows
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE e.e_ay_id = ?
						AND e.guidance_approved = ?
						AND e.e_registered = ?";
			if(in_array($where,range('a','z')))
			{
				$sql .= " AND p.lastname like ?";
				$query = $this->db->query($sql,array($this->c_sy->id,1,1,$where.'%'));
			}else{
				$query = $this->db->query($sql,array($this->c_sy->id,1,1));
			}
			return $query->num_rows() >= 1 ? $query->row()->num_rows : FALSE;
		}
	}
	/*****************************************************************************
		End for Approved and Enrolled student. Those with payments.
	*****************************************************************************/
	
	public function bonafide_students($start,$limit)
	{
		$start = is_numeric($start) ? intval($start) : 0;
		$limit = is_numeric($limit) ? intval($limit) : 0;
		
		if($this->c_e_gl != '' && !empty($this->c_e_gl)):
			$sql = "SELECT concat(p.firstname,' ',p.middlename,' ',p.lastname) as fullname,
					p.nationality,
					date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
					e.e_enrollee_type,
					level_desc,
					e.e_student_id as idno,
					e.e_id as enrollment_id,
					l.level_desc as level_code
					FROM enrollments e
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					WHERE e.e_grade_level IN ({$this->c_e_gl})
					AND registrar_approved = ?
					AND e.e_ay_id = ?
					AND e.verified = ?
					AND e.guidance_approved = ?
					AND e.e_registered = ?
					LIMIT {$limit} , {$start}";
			
			$query = $this->db->query($sql,array(1,$this->c_sy->id,1,1,1));
			return $query->num_rows() >= 1 ? $query->result() : FALSE;
		else:
			$sql = "SELECT concat(p.firstname,' ',p.middlename,' ',p.lastname) as fullname,
					p.nationality,
					date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
					e.e_enrollee_type,
					level_desc,
					e.e_student_id as idno,
					e.e_id as enrollment_id,
					l.level_desc as level_code
					FROM enrollments e
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					WHERE registrar_approved = ?
					AND e.e_ay_id = ?
					AND e.verified = ?
					AND e.guidance_approved = ?
					AND e.e_registered = ?
					LIMIT {$limit} , {$start}";
			
			$query = $this->db->query($sql,array(1,$this->c_sy->id,1,1,1));
			return $query->num_rows() >= 1 ? $query->result() : FALSE;
		endif;
	}
	
	public function search_bonafide_students($data)
	{
		if($data->level_id !== ''):
			$sql = "SELECT concat(p.firstname,' ',p.middlename,' ',p.lastname) as fullname,
					p.nationality,
					date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
					e.e_enrollee_type,
					level_desc,
					e.e_student_id as idno,
					e.e_id as enrollment_id,
					l.level_desc as level_code
					FROM enrollments e
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					WHERE (p.firstname LIKE ? OR p.lastname LIKE ? OR e.e_student_id LIKE ?  OR concat(p.firstname,' ',p.lastname) LIKE ?) 
					AND e.registrar_approved = ?
					AND e.e_grade_level = ?
					AND e.e_ay_id = ?
					AND e.verified = ?
					AND e.guidance_approved = ?
					AND e.e_registered = ?";
					
			$query = $this->db->query($sql,array('%'.$data->search_itm.'%',
												 '%'.$data->search_itm.'%',
												 '%'.$data->search_itm.'%',
												 '%'.$data->search_itm.'%',
												 1,
												 $data->level_id,
												 $this->c_sy->id,
												 1,
												 1,
												 1
												 ));
		else:
			if($this->c_e_gl != '' && !empty($this->c_e_gl)):
				$sql = "SELECT concat(p.firstname,' ',p.middlename,' ',p.lastname) as fullname,
						p.nationality,
						date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
						e.e_enrollee_type,
						level_desc,
						e.e_student_id as idno,
						e.e_id as enrollment_id,
						l.level_desc as level_code
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE (p.firstname LIKE ? OR p.lastname LIKE ? OR e.e_student_id LIKE ? OR concat(p.firstname,' ',p.lastname) LIKE ?) 
						AND e.e_grade_level IN ({$this->c_e_gl})
						AND e.registrar_approved = ?
						AND e.e_ay_id = ?
						AND e.verified = ?
						AND e.guidance_approved = ?
						AND e.e_registered = ?";
						
				$query = $this->db->query($sql,array('%'.$data->search_itm.'%',
													 '%'.$data->search_itm.'%',
													 '%'.$data->search_itm.'%',
													 '%'.$data->search_itm.'%',
													 1,
													 $this->c_sy->id,
													 1,
													 1,
													 1));
			else:
				$sql = "SELECT concat(p.firstname,' ',p.middlename,' ',p.lastname) as fullname,
						p.nationality,
						date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
						e.e_enrollee_type,
						level_desc,
						e.e_student_id as idno,
						e.e_id as enrollment_id,
						l.level_desc as level_code
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						WHERE 
						(p.firstname LIKE ? OR p.lastname LIKE ? OR e.e_student_id LIKE ? OR concat(p.firstname,' ',p.lastname) LIKE ?) 
						AND e.registrar_approved = ?
						AND e.e_ay_id = ?
						AND e.verified = ?
						AND e.guidance_approved = ?
						AND e.e_registered = ?";
						
				$query = $this->db->query($sql,array('%'.$data->search_itm.'%',
													 '%'.$data->search_itm.'%',
													 '%'.$data->search_itm.'%',
													 '%'.$data->search_itm.'%',
													 1,
													 $this->c_sy->id,
													 1,
													 1,
													 1));
			endif;
		endif;
		
		return $query-> num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function count_bonafide_students()
	{
		if($this->c_e_gl != '' && !empty($this->c_e_gl)):
					
			$sql = "SELECT count(e_id) as num_rows
					FROM enrollments
					WHERE e_grade_level IN ({$this->c_e_gl})
					AND registrar_approved = ?
					AND e_ay_id = ?
					AND verified = ?
					AND guidance_approved = ?
					AND e_registered = ?";
			$query = $this->db->query($sql,array(1,$this->c_sy->id,1,1,1));
			return $query->num_rows() >= 1 ? $query->row()->num_rows : FALSE ;
		
		else:
		
			$sql = "SELECT count(e_id) as num_rows
					FROM enrollments 
					WHERE registrar_approved = ?
					AND e_ay_id = ?
					AND verified = ?
					AND guidance_approved = ?
					AND e_registered = ?";
			$query = $this->db->query($sql,array(1,$this->c_sy->id,1,1,1));
			return $query->num_rows() >= 1 ? $query->row()->num_rows : FALSE ;
			
		endif;
	}
	
	/*****************************************************************************
		Deleted Enrollees
	*****************************************************************************/
	public function search_deleted_enrollee($ajax = FALSE,$data)
	{
		if($ajax == FALSE)
		{
			$sql = "SELECT concat_ws('',UPPER(p.firstname),' ',UPPER(p.lastname)) as fullname,
					e.student_id as idno,
					e.e_id as enrollment_id,
					date_format(e.created,'%M %e, %Y %h:%i %p') as date_enrolled,
					bs.block_name
					FROM enrollment_bin e
					LEFT JOIN profiles p ON e.p_id = p.profile_id
					LEFT JOIN block_section bs ON e.block = bs.id
					WHERE e.e_id = ?
					ORDER BY p.lastname
					";
					
			$query = $this->db->query($sql,array($data));
		}else{
			$sql = "SELECT  e.student_id as value,
							concat_ws('','[',LOWER(p.gender),'] ',UPPER(p.lastname),' , ',UPPER(p.firstname),' ',UPPER(p.middlename),' | ',LOWER(e.student_id)) as label,
							e.e_id as id,
							concat_ws('', LOWER(e.e_id),' - ',UPPER(p.lastname),' , ',UPPER(p.firstname) ) as format
					FROM enrollment_bin e
					LEFT JOIN profiles p ON e.p_id = p.profile_id
					LEFT JOIN block_section bs ON e.block = bs.id
					WHERE 		
					(   p.firstname LIKE ? 
						OR p.lastname LIKE ? 
						OR p.middlename LIKE ? 
						OR e.student_id LIKE ? 
						OR concat_ws('',p.firstname,' ',p.middlename,' ',p.lastname) LIKE ?
						OR concat_ws('',p.lastname,' ',p.firstname,' ',p.middlename) LIKE ?
					) 
					ORDER BY p.lastname
					";
					
			$query = $this->db->query($sql,array('%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%',
												 '%'.$data.'%'));
		}
		
		return $query-> num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function deleted_enrollees($start,$limit,$where)
	{
		$start = is_numeric($start)? intval($start) : 0;
		$limit = is_numeric($limit) ? intval($limit) : 0;

		$sql = "SELECT concat_ws('',p.lastname,' , ',p.firstname,' ',p.middlename) as fullname,
				e.student_id as idno,
				e.e_id as enrollment_id,
				date_format(e.created,'%b %e, %Y %h:%i %p') as date_enrolled,
				bs.block_name
                FROM enrollment_bin e
                LEFT JOIN profiles p ON e.p_id = p.profile_id
				LEFT JOIN block_section bs ON e.block = bs.id";

		if(in_array(strtolower($where),range('a','z')))
		{
			$sql .= " WHERE p.lastname like ?
					  ORDER BY p.lastname,e.created
					  LIMIT {$limit} , {$start}";
			$query = $this->db->query($sql,array($where.'%'));
		}else{
			$sql .= " ORDER BY p.lastname,e.created
					  LIMIT {$limit} , {$start}";
			$query = $this->db->query($sql);
		}

		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function count_deleted_enrollees($where)
	{
		$sql = "SELECT count(e.e_id) as numrows
                FROM enrollment_bin e
                LEFT JOIN profiles p ON e.p_id = p.profile_id
				";
		if(in_array(strtolower($where),range('a','z')))
		{
			$sql .= " AND p.lastname like ?";
			$query = $this->db->query($sql,array($where.'%'));
		}else{
			$query = $this->db->query($sql);
		}
		
		return $query->num_rows() >= 1 ? $query->row()->numrows : FALSE;
	}
	/*****************************************************************************
		End for Deleted Enrollees
	*****************************************************************************/
	
	public function search_student_like($x)
	{
		$sql = "SELECT e.e_student_id as value,
					   concat_ws('','[',p.gender,'] ',p.lastname,' , ',p.firstname,' ',p.middlename,' | ',e.e_student_id,' | ',l.level_desc) as label,
					   e.e_id as id,
					   concat_ws('', e.e_id,' - ',p.lastname,' , ',p.firstname ) as format
				FROM enrollments e 
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				LEFT JOIN levels l ON l.level_id = e.e_grade_level
				WHERE (e.moved_to = 'yes' OR e.guidance_approved = 1) AND e.registrar_approved = 1
				AND (e.e_student_id LIKE ? OR concat(p.firstname,' ',p.middlename,' ',p.lastname) LIKE ?)
				ORDER BY p.lastname
				LIMIT 15";
				
		$result = $this->db->query($sql,array('%'.$x.'%','%'.$x.'%'));
		return $result->num_rows() >=1 ? $result->result_array() : FALSE;
		// return $this->db->last_query();
	}
	
	public function search_student_like_registrar_verified($x)
	{
		$sql = "SELECT e.e_student_id as value,
					   concat_ws('','[',p.gender,'] ',p.lastname,' , ',p.firstname,' ',p.middlename,' | ',e.e_student_id,' | ',l.level_desc) as label,
					   e.e_id as id,
					   concat_ws('', e.e_id,' - ',p.lastname,' , ',p.firstname ) as format
				FROM enrollments e 
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				LEFT JOIN levels l ON l.level_id = e.e_grade_level
				WHERE (e.moved_to = 'yes' OR e.guidance_approved = 1)
				AND e.registrar_approved = 1
				AND e.e_ay_id = ?
				AND (e.e_student_id LIKE ? 
					 OR concat_ws('',p.firstname,' ',p.middlename,' ',p.lastname) LIKE ?
					 OR concat_ws('',p.lastname,' ',p.firstname,' ',p.middlename) LIKE ?
					 OR p.lastname LIKE ?
					 OR p.lastname LIKE ?
					 OR p.lastname LIKE ?
					 )
				ORDER BY p.lastname
				LIMIT 15";
				
		$result = $this->db->query($sql,array($this->c_sy->id,'%'.$x.'%','%'.$x.'%','%'.$x.'%','%'.$x.'%','%'.$x.'%','%'.$x.'%'));
		return $result->num_rows() >=1 ? $result->result_array() : FALSE;
	}
	
	public function verified_enrollees_for_enrollmentlist($x = FALSE)
	{
		if($x == FALSE)
		{
			$sql = "SELECT concat_ws('',p.lastname,' , ',p.firstname,' ',p.middlename) as fullname,
					level_desc,
					e.e_student_id as idno,
					e.e_id as enrollment_id,
					l.level_desc as level_code,
					p.gender,
					p.age,
					l.level_code,
					date_format(p.dob,'%M %e, %Y') as dob,
					p.dob as bdate,
					date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
					p.nationality,
					e.registrar_approved,
					bs.block_name,
					sec.section_name
					FROM enrollments e
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN block_section bs ON e.block = bs.id
					LEFT JOIN sections sec ON sec.id = e.e_section_id
					WHERE e.e_archived = 0
					AND e.e_ay_id = ?
					AND verified = 1
					ORDER BY l.level_id,p.lastname";
			$query = $this->db->query($sql,array($this->c_sy->id));
		}else{
			$sql = "SELECT concat_ws('',p.firstname,' ',p.middlename,' ',p.lastname) as fullname,
					level_desc,
					e.e_student_id as idno,
					e.e_id as enrollment_id,
					l.level_desc as level_code,
					p.gender,
					p.age,
					date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
					p.nationality,
					e.registrar_approved,
					bs.block_name
					FROM enrollments e
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN block_section bs ON e.block = bs.id
					WHERE e.e_archived = 0
					AND e.unfinished = 0
					AND e.e_ay_id = ?
					AND verified = 1
					AND l.level_id = ?";
			$query = $this->db->query($sql,array($this->c_sy->id,$x));
		}
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	private function __masterlist($id)
	{
		$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
					level_desc,
					e.e_profile_id,
					e.e_student_id as idno,
					e.e_id as enrollment_id,
					l.level_code,
					p.gender,
					p.age,
					date_format(p.dob,'%M %e, %Y') as dob,
					date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
					p.nationality,
					bs.block_name,
					e.registrar_approved
					FROM enrollments e
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN block_section bs ON e.block = bs.id
					WHERE e.e_archived = 0
					AND l.level_id = ? 
					AND e.e_ay_id = ?
					ORDER BY p.gender,p.lastname,p.firstname,p.middlename";
					
		$query = $this->db->query($sql,array(intval($id),$this->c_sy->id));
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	private function __foreign($id)
	{
		$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
					level_desc,
					e.e_student_id as idno,
					e.e_id as enrollment_id,
					l.level_code,
					p.gender,
					p.age,
					p.dob,
					date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
					p.nationality,
					e.registrar_approved,
					bs.block_name
					FROM enrollments e
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN block_section bs ON e.block = bs.id
					WHERE e.e_archived = 0
					AND l.level_id = ? 
					AND e.e_ay_id = ?
					AND p.nationality != 'filipino'
					ORDER BY p.gender,p.lastname,p.firstname,p.middlename";
					
		$query = $this->db->query($sql,array(intval($id),$this->c_sy->id));
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	public function get_foreign_students($id = '')
	{
		$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname,' ',p.middlename) as fullname,
					level_desc,
					e.e_student_id as idno,
					e.e_id as enrollment_id,
					l.level_code,
					p.gender,
					p.age,
					p.dob,
					date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
					p.nationality,
					bs.block_name,
					f_ssp_status as ssp_stat,
					f_visa_status as visa_stat,
					f_authorized_stay as auth_stay,
					f_passport_no as pass_no,
					f_i_card_no as card_no,
					f_date_if_issue as date_issued,
					present_address
					FROM enrollments e
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN block_section bs ON e.block = bs.id
					WHERE e.e_archived = 0
					AND e.e_ay_id = ?
					AND p.nationality != 'filipino'
					ORDER BY p.gender,p.lastname,p.firstname,p.middlename";
					
		$query = $this->db->query($sql,array($this->c_sy->id));
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	private function __nonforeign($id)
	{
		$sql = "SELECT concat_ws('',p.lastname,' , ',p.middlename,' ',p.firstname) as fullname,
					level_desc,
					e.e_student_id as idno,
					e.e_id as enrollment_id,
					l.level_code,
					p.gender,
					p.age,
					p.dob,
					date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
					p.nationality,
					e.registrar_approved,
					bs.block_name
					FROM enrollments e
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN block_section bs ON e.block = bs.id
					WHERE e.e_archived = 0
					AND l.level_id = ? 
					AND e.e_ay_id = ?
					AND p.nationality = 'filipino'
					ORDER BY p.gender,fullname";
					
		$query = $this->db->query($sql,array(intval($id),$this->c_sy->id));
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	private function _count($level,$type='male',$foreign = TRUE,$other= ' ')
	{
		if($foreign === FALSE)
		{
			$sql = "SELECT gender,count(e_id) as total
					FROM enrollments e
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					WHERE e.e_archived = 0
					AND l.level_id = ? 
					AND e.e_ay_id = ?
					AND p.gender = ? {$other} ";
		}elseif($foreign === TRUE){
			$sql = "SELECT count(e_id) as total
					FROM enrollments e
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					WHERE e.e_archived = 0
					AND l.level_id = ? 
					AND e.e_ay_id = ?
					AND p.nationality != 'filipino'
					AND p.gender = ? {$other}";
		}elseif($foreign === 'nonforeign')
		{
			$sql = "SELECT count(e_id) as total
					FROM enrollments e
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					WHERE e.e_archived = 0
					AND l.level_id = ? 
					AND e.e_ay_id = ?
					AND p.nationality = 'filipino'
					AND p.gender = ? {$other}";
		}
		
		$query = $this->db->query($sql,array($level,$this->c_sy->id,$type));
		return $query->num_rows() >= 1 ? $query->row()->total : FALSE;
	}
	
	public function get_birthdays($year = FALSE,$month = FALSE)
	{
		if($year AND $month)
		{
			
				$sql = "SELECT concat_ws('',p.firstname,' ',p.lastname) as fullname,
							level_desc,
							e.e_student_id as idno,
							e.e_id as enrollment_id,
							l.level_desc as level_code,
							p.nationality,
							day(p.dob) as day_of_birth,
							bs.block_name
							FROM enrollments e
							LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
							LEFT JOIN levels l ON e.e_grade_level = l.level_id
							LEFT JOIN block_section bs ON e.block = bs.id
							WHERE e.registrar_approved = 1
							AND month(p.dob) = ?
							AND e.e_archived = 0
							AND e.e_ay_id = ?
							ORDER BY p.lastname ASC,l.level_desc ASC";
							
				$result = $this->db->query($sql,array($month,$this->c_sy->id));
				
				if($result->num_rows() >= 1)
				{
					foreach($result->result() as $k => $v)
					{
						$birthdays[$v->day_of_birth][]= ucwords(strtolower($v->fullname)).'**'.$v->enrollment_id;
					}
					
					return $birthdays;
				}else{
					return array();
				}
			
		
		}else{
			return array();
		}
	}
	
	public function get_birthdays_csv($year = FALSE,$month = FALSE)
	{
		if($year AND $month)
		{
			
			$sql = "SELECT p.lastname, 
						p.firstname,
						level_desc,
						e.e_student_id as idno,
						l.level_desc as level_code,
						p.gender,
						p.dob as day_of_birth
						FROM enrollments e
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						LEFT JOIN block_section bs ON e.block = bs.id
						WHERE e.registrar_approved = 1
						AND month(p.dob) = ?
						AND e.e_archived = 0
						AND e.e_ay_id = ?
						ORDER BY day(p.dob), p.lastname ASC";
						
			$result = $this->db->query($sql,array($month,$this->c_sy->id));

			if($result->num_rows() >= 1)
			{
				return $result->result();
			}else{
				return array();
			}
			
		}else{
			return array();
		}
	}
	
	public function default_masterlist($x = 'default')
	{
		$ci =& get_instance();
		$ci->load->model('M_levels','ml');
		$levels = $ci->ml->get_all_levels();
	
		if($levels)
		{
			$method = strtolower($x) == 'default' ? '__masterlist' : '__foreign';
			
			switch(strtolower($x))
			{
				case $x == 'default':
					$method = '__masterlist';
				break;
				case $x == 'foreign':
					$method = '__foreign';
				break;
				case $x == 'nonforeign':
					$method = '__nonforeign';
				break;
				case $x == 'rverified':
					$method = '__rverified';
				break;
				case $x == 'foreignstatus':
					$method = '__foreign_with_status';
				break;
			
			}
			
			$type = in_array(strtolower($x),array('default','nonforeign','rverified')) ? FALSE : TRUE;

			if(in_array(strtolower($x),array('default')))
			{
				$type = FALSE;
				$other = ' ';
			}elseif(in_array(strtolower($x),array('rverified')))
			{
				$type= FALSE;
				$other = ' AND registrar_approved = 1';
			}elseif(in_array(strtolower($x),array('nonforeign')))
			{
				$type = 'nonforeign';
				$other = ' ';
			}else{
				$type = TRUE;
				$other = ' ';
			}
			
			
			foreach($levels as $k => $v)
			{
				$students = $this->$method(intval($v->level_id));
				if(!empty($students))
				{
					$data[$k][$v->level_desc]['level_code'] = $v->level_code;
					$data[$k][$v->level_desc]['students'] = $this->$method(intval($v->level_id));
					$data[$k][$v->level_desc]['male_count'] = $this->_count(intval($v->level_id),'male',$type,$other);
					$data[$k][$v->level_desc]['female_count'] = $this->_count(intval($v->level_id),'female',$type,$other);
				}
			}
			
			if(!empty($data)){
				return $data;
			}else{
				return FALSE;
			}
			
		}else{
			return FALSE;
		}
	}
	
	public function get_siblings_all($letter= FALSE)
	{
		$type= 'lastname';
		$get = "SELECT distinct {$type} FROM profiles 
	 				LEFT JOIN enrollments e ON e.e_profile_id = profiles.profile_id
	 				WHERE e.e_ay_id = ?
	 				AND e.e_archived = 0
	 				AND registrar_approved = 1
	 				AND verified = 1
	 				order by {$type}";

		$by = $this->db->query($get,[$this->c_sy->id]);
		
		foreach($by->result() as $k => $v)
		{
			// $data[$key] = $this->db->last_query();
			$data[$v->$type][] = $this->_get_siblings($type,$v->$type);
		}

		return $data;
	}
	
	public function count_siblings($type,$lastname)
	{
		$type = 'lastname';
		// $restrictions = ['mothername','fathername'];
		$restrictions = ['mothername','lastname'];
		if(in_array(strtolower($type),$restrictions))
		{

			$get = "SELECT distinct {$type} FROM profiles 
					LEFT JOIN enrollments e ON e.e_profile_id = profiles.profile_id
					WHERE e.e_ay_id = ?
					AND {$type} != ''
					AND LENGTH({$type}) >=2
					AND {$type} like ?
					order by {$type}";
			$by = $this->db->query($get,[$this->c_sy->id,$lastname.'%']);

			return $by->num_rows();
		}else{
			return FALSE;
		}
	}
	
	public function get_siblings($type,$lastname,$start,$limit)
	{
		$start = is_numeric($start) ? intval($start) : 0;
		$limit = is_numeric($limit) ? intval($limit) : 0;
		$type = 'lastname';

		$get = "SELECT distinct {$type} FROM profiles 
	 				LEFT JOIN enrollments e ON e.e_profile_id = profiles.profile_id
	 				WHERE e.e_ay_id = ?
	 				AND {$type} != ''
	 				AND LENGTH({$type}) >=2
	 				AND {$type} like ?
	 				order by {$type}
	 				LIMIT {$limit} , {$start}";
		$by = $this->db->query($get,[$this->c_sy->id,$lastname.'%']);

		if($by->num_rows() >=1)
 		{
 			foreach($by->result() as $k => $v)
 			{
 				$siblings[$k][$v->$type] = $this->_get_siblings($type,$v->$type);
			}
			

 			return $siblings;
 		}else{
 			return FALSE;
 		}
	}
	
	private function _get_siblings($type,$data)
	{
		$get_siblings = "SELECT concat_ws('',p.lastname,', ',p.firstname) as fullname,
						concat_ws('',p.mothername,' ',p.mother_lname) as mothername,
						concat_ws('',p.fathername,' ',p.father_lname) as fathername,
						concat_ws('',p.step_mother_name,' ',p.step_mother_lname) as stepmothername,
						concat_ws('',p.step_father_name,' ',p.step_father_lname) as stepfathername,
						level_desc,
						e.e_student_id as idno,
						e.e_id as enrollment_id,
						l.level_desc as level_code,
						date_format(e.e_created_at,'%M %e, %Y %h:%i %p') as date_enrolled,
						p.nationality,
						bs.block_name
				      FROM enrollments e
						LEFT JOIN levels l ON e.e_grade_level = l.level_id
						LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
						LEFT JOIN block_section bs ON e.block = bs.id
						WHERE p.{$type} = ?";

		$by = $this->db->query($get_siblings,[$data]);


		if($by->num_rows() >=1)
		{
			foreach($by->result() as $k => $v)
			{
				$fathername = $v->fathername == NULL ? $v->stepfathername : $v->fathername;
				$mothername = $v->mothername == NULL ? $v->stepmothername : $v->mothername;
				$stud[$fathername]['children'][$v->enrollment_id] = ucwords(strtolower($v->fullname));
				$stud[$fathername]['gradelevel'][$v->enrollment_id] = ucwords(strtolower($v->level_desc));
				$stud[$fathername]['mother'] = $mothername;
			}	
		}
		return $stud;

	}
	
	public function get_students_for_parent_login($name)
	{
		$login = 'SELECT p_id FROM parent_child
					 GROUP BY p_id';
		$r_login = $this->db->query($login);

		if($r_login->num_rows() >=1)
		{
			foreach($r_login->result() as $k => $v)
			{
				$e_ids_array[] = $v->p_id; 
			}
				$e_ids = implode(',',array_map('intval',$e_ids_array));
		}else{
				$e_ids = 0;
		}

		$sql = "SELECT e.e_student_id as value,
						   concat_ws('','[',p.gender,'] ',p.lastname,', ',p.firstname,' ',p.middlename,' | ',e.e_student_id,' | ',l.level_desc) as label,
						   e.e_id as id,
						   concat_ws('', e.e_id,' - ',p.lastname,' , ',p.firstname ) as format,
						   p.profile_id as p_id
					FROM enrollments e
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					LEFT JOIN block_section bs ON e.block = bs.id
					LEFT JOIN sections sec ON e.e_section_id = sec.id
					WHERE 
						(p.firstname LIKE ? 
						OR p.lastname LIKE ? 
						OR p.middlename LIKE ? 
						OR e.e_student_id LIKE ? 
						OR concat_ws('',p.firstname,' ',p.middlename,' ',p.lastname) LIKE ?
						OR concat_ws('',p.lastname,' ',p.firstname,' ',p.middlename) LIKE ?)
					AND e.e_archived = 0
					AND e.e_ay_id = {$this->c_sy->id}
					AND registrar_approved = 1
					AND verified = 1
					AND p.profile_id NOT IN ({$e_ids})
					ORDER BY p.lastname 
					LIMIT 15 ";
		$result = $this->db->query($sql,explode(',',str_repeat('%'.$name.'%,', 6)));
		// return $this->db->last_query();
		return $result->num_rows() >=1 ? $result->result() : FALSE;
	}
	
	public function count_contacts($lname)
	{
		$sql = "SELECT count(e.e_id) as sum
				FROM enrollments e
				LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
				WHERE e.e_archived = 0
				AND e.e_ay_id = ?
				AND registrar_approved = 1
				AND verified = 1";

		if($lname !== 'all' AND in_array($lname,range('a','z')))
		{
			$sql .= " AND p.lastname LIKE '%{$lname}' ";
		}

		$res = $this->db->query($sql,[$this->c_sy->id]);
		return $res->num_rows() >=1 ? $res->row()->sum : 0;
	}
	
	public function get_contacts($start,$limit,$lname)
	{
		$start = intval($start);
		$limit = intval($limit);
		// echo $start;
		// echo $limit;
		$sql = "SELECT p.lastname,
					   p.email,
					   p.contact_no as num,
					   p.present_address as addr,
					   concat_ws(' ',p.father_office_num,' ',p.father_contact_no) as father_contact,
					   concat_ws(' ',p.mother_office_num,' ',p.mother_contact_no) as mother_contact
				FROM enrollments e
				LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
				WHERE e.e_archived = 0
				AND e.e_ay_id = ?
				AND registrar_approved = 1
				AND verified = 1";

		if($lname !== 'all' AND in_array($lname,range('a','z')))
		{
			$sql .= " AND p.lastname LIKE '%{$lname}' ";
		}

		$sql .=" ORDER BY p.lastname 
				LIMIT {$limit},{$start}";

		$res = $this->db->query($sql,[$this->c_sy->id]);

		// pd($this->db->last_query());
		return $res->num_rows() >=1 ? $res->result() : FALSE;
	}
	
	public function get_csv($type)
	{
		$sql = "SELECT p.lastname,
					   p.email,
					   p.contact_no as num,
					   p.present_address as addr,
					   concat(p.fathername,' ',p.mothername) as parents,
					   concat_ws(' ',p.father_office_num,' ',p.father_contact_no) as father_contact,
					   concat_ws(' ',p.mother_office_num,' ',p.mother_contact_no) as mother_contact
				FROM enrollments e
				LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
				WHERE e.e_archived = 0
				AND e.e_ay_id = ?
				AND registrar_approved = 1
				AND verified = 1";
		$res = $this->db->query($sql,[$this->c_sy->id]);

		if($res->num_rows() >=1)
		{
			if($type== 'gmail'){
				$csv = 'Name,Email Address'.PHP_EOL;
				foreach($res->result() as $v)
				{
					$parents = str_replace(',','',$v->parents);
					$csv .= " MR and Mrs {$parents},{$v->email}".PHP_EOL;
				}
			}elseif ($type == 'yahoo') {
				$csv = 'First,Middle,Last,Nickname,Email'.PHP_EOL;
				foreach($res->result() as $v)
				{
					$parents = str_replace(',','', $v->parents);
					$csv .= " MR and Mrs {$parents},,,,{$v->email}".PHP_EOL;
				}
			}	

			return $csv;
		}else{
			return FALSE;
		}
	}
	
	public function count_all_students_per_grade_level($verified = FALSE)
	{
		$CI =& get_instance();
		
		$CI->load->model('M_levels','mlev');
		
		$levels = $CI->mlev->get_all_levels();
		
		if($levels)
		{
			foreach($levels as $k => $val)
			{
				if(in_array($val->level_category,['highschool','secondary']))
				{
					$var = 'SECONDARY';
				}else{
					$var = 'Preschool/Elementary';
				}
				$data[$var][$val->level_desc]['male'] = $this->count_students_in_level($val->level_id,'male',$verified);
				$data[$var][$val->level_desc]['female'] = $this->count_students_in_level($val->level_id,'female',$verified);
			}
		}
		
		return $data;
	}
	
	private function count_students_in_level($level_id = '',$gender = '',$verified)
	{
		$sql = 'SELECT count(e_id) as total_students 
				FROM enrollments e
				LEFT JOIN levels l ON l.level_id = e.e_grade_level
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				WHERE p.gender = ?
				AND e.verified = 1
				AND e.e_ay_id = ?
				AND e.e_grade_level = ?';
				
		if($verified == FALSE)
		{
			$sql .= ' AND e.registrar_approved = 1';
		}
				
		$query = $this->db->query($sql,array($gender,$this->c_sy->id,$level_id));
		
		if($query->num_rows() >= 1)
		{
			return $query->row()->total_students;
		}else{
			return 0;
		}
	}
	
	public function count_students_by_level_type($gender,$level_type = '',$verified = FALSE)
	{
		
	
		if(is_array($level_type))
		{
		
			$level_type = implode('" , "',$level_type);
			$sql = 'SELECT count(e_id) as total_students 
					FROM enrollments e
					LEFT JOIN levels l ON l.level_id = e.e_grade_level
					LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
					WHERE p.gender = ?
					AND e.verified = 1
					AND e.e_ay_id = ?
					AND l.level_category IN ("'.$level_type.'")';
					
			
			
			if($verified == FALSE)
			{
				$sql .= 'AND e.registrar_approved = 1';
			}
			
			$query = $this->db->query($sql,array($gender,$this->c_sy->id,$level_type));
			
			if($query->num_rows() >= 1)
			{
				return $query->row()->total_students;
			}else{
				return 0;
			}
			
		}else{
	
			$sql = 'SELECT count(e_id) as total_students 
					FROM enrollments e
					LEFT JOIN levels l ON l.level_id = e.e_grade_level
					LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
					WHERE p.gender = ?
					AND e.verified = 1
					AND e.e_ay_id = ?
					AND l.level_category = ?';
					
			if($verified == FALSE)
			{
				$sql .= 'AND e.registrar_approved = 1';
			}
			
			$query = $this->db->query($sql,array($gender,$this->c_sy->id,$level_type));
			
			if($query->num_rows() >= 1)
			{
				return $query->row()->total_students;
			}else{
				return 0;
			}
			
		}
	}
	
	/*----------------------------------------------------------------------------
		Student with Balance
	-----------------------------------------------------------------------------*/
	public function count_student_with_balance($letter)
	{
		$e_ids = "SELECT sum(spr_ammt_paid) as total,
			 spr_enrollment_id
		  FROM student_payment_records
		  WHERE spr_schoolyear_id = ?
		  GROUP BY spr_enrollment_id
		  HAVING total < (
			SELECT sum(sef_fee_rate) FROM student_enrollment_fees WHERE sef_schoolyear_id = ? AND sef_enrollment_id = spr_enrollment_id GROUP BY sef_enrollment_id
		  )";
		
		$studwithbal = $this->db->query($e_ids,array($this->c_sy->id,$this->c_sy->id));

		$studwithbalr = $studwithbal->result();
		
		if($studwithbalr){
			foreach($studwithbalr as $k => $v)
			{
				$e_ids_array[] = $v->spr_enrollment_id;
			}

			$e_ids_formatted = implode(',',array_map('intval',$e_ids_array));
			$sql = "SELECT count(e.e_id) as numrows
					FROM enrollments e
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					WHERE registrar_approved = 1
					AND e.e_ay_id = ?
					AND e.e_id IN ({$e_ids_formatted})
					AND lastname like ?";
			$query = $this->db->query($sql,array($this->c_sy->id,$letter.'%'));
			return $query->num_rows() >=1 ? $query->row()->numrows : 0;
		}else{
			return 0;
		}
	}
	
	public function student_with_balance($start,$limit,$letter)
	{
		$start = is_numeric($start)? intval($start) : 0;
		$limit = is_numeric($limit) ? intval($limit) : 0;
			
		$e_ids = "SELECT sum(spr_ammt_paid) as total,
			 spr_enrollment_id
		  FROM student_payment_records
		  WHERE spr_schoolyear_id = ?
		  GROUP BY spr_enrollment_id
		  HAVING total < (
			SELECT sum(sef_fee_rate) FROM student_enrollment_fees WHERE sef_schoolyear_id = ? AND sef_enrollment_id = spr_enrollment_id GROUP BY sef_enrollment_id
		  )";
		
		$studwithbal = $this->db->query($e_ids,array($this->c_sy->id,$this->c_sy->id));

		$enrollments = $studwithbal->result();
		
		if($enrollments){
			foreach($enrollments as $k => $v)
			{
				$e_ids_array[] = $v->spr_enrollment_id;
			}

			$e_ids_formatted = implode(',',array_map('intval',$e_ids_array));


			$sql = "SELECT concat_ws('',p.lastname,', ',p.firstname) as fullname,level_desc,e.e_student_id,e.e_id as enrollment_id
					FROM enrollments e
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					WHERE registrar_approved = 1
					AND e.e_ay_id = ?
					AND e.e_id IN ({$e_ids_formatted})
					AND p.lastname like ?
					LIMIT {$limit} , {$start}";
			$query = $this->db->query($sql,array($this->c_sy->id,$letter.'%'));
			return $query->num_rows() >= 1 ? $query->result() : FALSE;
		}else{
			return 0;
		}
	}
	
	public function count_student_with_balance_2($letter=FALSE)
	{
		$e_ids = "SELECT sum(spr_ammt_paid) as total,
			 spr_enrollment_id
		  FROM student_payment_records
		  WHERE spr_schoolyear_id = ?
		  GROUP BY spr_enrollment_id
		  HAVING total < (
			SELECT sum(sef_fee_rate) FROM student_enrollment_fees WHERE sef_schoolyear_id = ? AND sef_enrollment_id = spr_enrollment_id GROUP BY sef_enrollment_id
		  )";
		
		$studwithbal = $this->db->query($e_ids,array($this->c_sy->id,$this->c_sy->id));

		$studwithbalr = $studwithbal->result();
		
		if($studwithbalr)
		{
			foreach($studwithbalr as $k => $v)
			{
				$e_ids_array[] = $v->spr_enrollment_id;
			}

			$e_ids_formatted = implode(',',array_map('intval',$e_ids_array));
			$sql = "SELECT count(e.e_id) as numrows
					FROM enrollments e
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					WHERE registrar_approved = 1
					AND e.e_ay_id = ?
					AND e.e_id IN ({$e_ids_formatted})
					";
			$query = $this->db->query($sql,array($this->c_sy->id));
			return $query->num_rows() >=1 ? $query->row()->numrows : 0;
		}else{
			return 0;
		}
	}
	/*----------------------------------------------------------------------------
		End Student with Balance
	-----------------------------------------------------------------------------*/
	
	public function search_enrolled()
	{
		$sy = $this->o_sy;
		$ay_id = $sy->id;
		$new_val = array();
		
		$q = $this->db->select('e_profile_id')
					  ->where('e_ay_id',$ay_id)
					  ->get('enrollments');
		
		if( $q->num_rows() >=1 )
		{
			foreach($q->result() as $val)
			{
				array_push($new_val,$val->e_profile_id);
			}
			return $new_val;
		}else{
			return $new_val;
		}
	}
}